CREATE TABLE pais (
    idPais NUMERIC(2) NOT NULL,
    nomPais VARCHAR(20) NOT NULL,
    CONSTRAINT PK_pais PRIMARY KEY (idPais)
	);



CREATE TABLE ciudad (
    idCiudad  NUMERIC(5) NOT NULL,
    nomCiudad VARCHAR(20) NOT NULL,
    idPais NUMERIC(2) NOT NULL,
    CONSTRAINT PK_ciudad PRIMARY KEY (idCiudad),
	CONSTRAINT FK_ciudad_idPais FOREIGN KEY (idPais) REFERENCES pais (idPais) 
	);



CREATE TABLE hotel (
    idHotel    NUMERIC(5) NOT NULL,
    nomHotel   VARCHAR(30) NOT NULL,
    idCiudad   NUMERIC(5) NOT NULL,
	CONSTRAINT PK_hotel PRIMARY KEY (idHotel),
	CONSTRAINT FK_hotel_idCiudad FOREIGN KEY (idCiudad) REFERENCES ciudad(idCiudad)
	);



CREATE TABLE tipohab (
	idHotel      NUMERIC(5) NOT NULL,
   	idTipoHab    CHAR(3) NOT NULL, 
    CONSTRAINT   PK_tipohab PRIMARY KEY (idHotel,idTipoHab)
    );	



CREATE TABLE habitacion (
	idHotel       NUMERIC(5) NOT NULL,
    numHabitacion NUMERIC(5) NOT NULL,
	idTipoHab     CHAR(3) NOT NULL,
    cantCamas     NUMERIC(2) NOT NULL,	
	CONSTRAINT    PK_habitacion PRIMARY KEY (idHotel, numHabitacion),
	CONSTRAINT    FK_habitacion_idhotel_idtipoHab FOREIGN KEY (idHotel,idTipoHab) REFERENCES tipoHab (idHotel,idTipoHab),
	CONSTRAINT	  FK_habitacion_idhotel_numhabitacion FOREIGN KEY (idHotel) REFERENCES hotel(idHotel) 
	);



CREATE TABLE usohab (
	idHotel       NUMERIC(5) NOT NULL,
    numHabitacion NUMERIC(5) NOT NULL,
	fecha         DATE NOT NULL, 	
	ocupada       bit,
	CONSTRAINT    PK_usohab PRIMARY KEY (idHotel, numHabitacion, fecha),
    CONSTRAINT	  FK_usohab_hotel_numhabitacion FOREIGN KEY (idHotel, numHabitacion) REFERENCES habitacion(idHotel, numHabitacion) 
	);


CREATE TABLE usuario (
	usremail   CHAR(25) NOT NULL,
	usrpwd     CHAR(12) NOT NULL, 
    CONSTRAINT   PK_usuario PRIMARY KEY (usremail)
    );	



CREATE TABLE regimen (
	idRegimen    CHAR(2) NOT NULL,
	descrRegimen CHAR(30) NOT NULL, 
    CONSTRAINT   PK_regimen PRIMARY KEY (idRegimen)
    );	




CREATE TABLE reserva (
    idReserva     NUMERIC(12,0) IDENTITY (1,1),
	usremail      CHAR(25),
	idHotel       NUMERIC(5) NOT NULL,
    numHabitacion NUMERIC(5) NOT NULL,
    canthuesp     NUMERIC(1) NOT NULL,
	idRegimen     CHAR(2) NOT NULL,
	fchReserva    DATE NOT NULL,
	fchDesde      DATE NOT NULL,
	fchHasta      DATE NOT NULL,
	CONSTRAINT    PK_reserva PRIMARY KEY (idReserva),
	CONSTRAINT    FK_reserva_usremail FOREIGN Key (usremail) REFERENCES usuario (usrEmail),
	CONSTRAINT    FK_reserva_idhotel_numHabitacion FOREIGN KEY (idHotel,numHabitacion) REFERENCES habitacion (idHotel,numHabitacion),
	CONSTRAINT    FK_reserva_idregimen FOREIGN KEY (idRegimen) REFERENCES regimen (idRegimen)
	);



CREATE TABLE huesped (
	idHuesped     NUMERIC(12) NOT NULL,
    nomHuesped    VARCHAR(15) NOT NULL,
	apePaterno    VARCHAR(15) NOT NULL,
	apeMaterno    VARCHAR(15),
	tipoDocumento CHAR(8) NOT NULL,
	numDocumento  NUMERIC(15) NOT NULL,
	email         VARCHAR(30),
	tel           NUMERIC(15) NOT NULL
	CONSTRAINT PK_huesped PRIMARY KEY (idHuesped),
	);	






CREATE TABLE prepago (
    idPrepago     NUMERIC(12,0) IDENTITY (1,1),
	usremail      CHAR(25),
	idHotel       NUMERIC(5) NOT NULL,
    numHabitacion NUMERIC(5) NOT NULL,
	idRegimen     CHAR(2) NOT NULL,
	fchPrepago    DATE NOT NULL,
	fchDesde      DATE NOT NULL,
	fchHasta      DATE NOT NULL,
	CONSTRAINT    PK_prepago PRIMARY KEY (idPrepago),
	CONSTRAINT    FK_prepago_usremail FOREIGN Key (usremail) REFERENCES usuario (usrEmail),
	CONSTRAINT    FK_prepago_idhotel_numhabitacion FOREIGN KEY (idHotel,numHabitacion) REFERENCES habitacion(idHotel,numHabitacion),
	CONSTRAINT    FK_prepago_idregimen FOREIGN KEY (idRegimen) REFERENCES regimen (idRegimen)
	);



	CREATE TABLE prephues (
    idPrepago     NUMERIC(12,0) NOT NULL,
	idHuesped     NUMERIC(12) NOT NULL,
	CONSTRAINT    PK_prephues PRIMARY KEY (idPrepago, idHuesped),
	CONSTRAINT    FK_prephues_idprepago FOREIGN KEY (idPrepago) REFERENCES prepago (idPrepago),
	CONSTRAINT    FK_prepago_idhuesped  FOREIGN KEY (idHuesped) REFERENCES huesped (idHuesped)
	);